/*
Navicat SQL Server Data Transfer

Source Server         : LOCALHOST
Source Server Version : 110000
Source Host           : .:1433
Source Database       : AAACHAM2
Source Schema         : dbo

Target Server Type    : SQL Server
Target Server Version : 110000
File Encoding         : 65001

Date: 2014-04-25 14:00:46
*/


-- ----------------------------
-- Table structure for tb3538
-- ----------------------------
DROP TABLE [dbo].[tb3538]
GO
CREATE TABLE [dbo].[tb3538] (
[TransactionID] varchar(20) NOT NULL ,
[POrderID] varchar(20) NULL ,
[InventoryID] varchar(20) NULL ,
[MethodID] varchar(20) NULL ,
[OrderQuantity] decimal(28,8) NULL ,
[OriginalAmount] money NULL ,
[ConvertedAmount] money NULL ,
[PurchasePrice] money NULL ,
[VATPercent] money NULL ,
[VATConvertedAmount] money NULL ,
[DiscountPercent] money NULL ,
[DiscountConvertedAmount] money NULL ,
[IsPicking] tinyint NOT NULL ,
[Quantity01] money NULL ,
[Quantity02] money NULL ,
[Quantity03] money NULL ,
[Quantity04] money NULL ,
[Quantity05] money NULL ,
[Quantity06] money NULL ,
[Quantity07] money NULL ,
[Quantity08] money NULL ,
[Quantity09] money NULL ,
[Quantity10] money NULL ,
[Quantity11] money NULL ,
[Quantity12] money NULL ,
[Quantity13] money NULL ,
[Quantity14] money NULL ,
[Quantity15] money NULL ,
[Quantity16] money NULL ,
[Quantity17] money NULL ,
[Quantity18] money NULL ,
[Quantity19] money NULL ,
[Quantity20] money NULL ,
[Quantity21] money NULL ,
[Quantity22] money NULL ,
[Quantity23] money NULL ,
[Quantity24] money NULL ,
[Quantity25] money NULL ,
[Quantity26] money NULL ,
[Quantity27] money NULL ,
[Quantity28] money NULL ,
[Quantity29] money NULL ,
[Quantity30] money NULL ,
[WareHouseID] varchar(20) NULL ,
[DiscountOriginalAmount] money NULL ,
[VATOriginalAmount] money NULL ,
[Orders] int NULL ,
[Description] nvarchar(250) NULL ,
[Ana01ID] varchar(20) NULL ,
[Ana02ID] varchar(20) NULL ,
[Ana03ID] varchar(20) NULL ,
[Ana04ID] varchar(20) NULL ,
[Ana05ID] varchar(20) NULL ,
[AdjustQuantity] money NULL ,
[InventoryCommonName] nvarchar(250) NULL ,
[UnitID] varchar(20) NULL ,
[Finish] tinyint NULL ,
[Notes] nvarchar(250) NULL ,
[Notes01] nvarchar(250) NULL ,
[Notes02] nvarchar(250) NULL ,
[RefTransactionID] varchar(20) NULL ,
[ROrderID] varchar(20) NULL ,
[ConvertedQuantity] decimal(13) NULL ,
[ImTaxPercent] money NULL ,
[ImTaxOriginalAmount] money NULL ,
[ImTaxConvertedAmount] money NULL 
)


GO

-- ----------------------------
-- Triggers structure for table tb3538
-- ----------------------------
--DROP TRIGGER [dbo].[OX3002]
GO
CREATE TRIGGER [dbo].[OX3002]
ON [dbo].[tb3538]
AFTER DELETE
AS
Declare @tb3529_cur as cursor,
	@PQuantity as decimal(28,2),
	@InventoryID as varchar(20),
	@WareHouseID as varchar(20),
	@DivisionID as varchar(20),
	@IsPicking as tinyint, 
	@POrderID varchar(20)

Set @tb3529_cur = cursor scroll keyset for
		Select Del.POrderID, DivisionID, WareHouseID, InventoryID, OrderQuantity, IsPicking
		From Deleted Del inner join tb3537 on Del.POrderID = tb3537.POrderID 
		Where Isnull(WareHouseID,'') <> ''
		

Open @tb3529_cur 
Fetch next from @tb3529_cur into @POrderID, @DivisionID, @WareHouseID, @InventoryID, @PQuantity, @IsPicking
While @@Fetch_Status = 0
Begin
	If not exists(Select Top 1 1 From tb3529 Where DivisionID = @DivisionID and WareHouseID = @WareHouseID and
						InventoryID = @InventoryID) 
		Insert tb3529(DivisionID, WarehouseID, InventoryID, SQuantity, PQuantity) values
			(@DivisionID, @WarehouseID, @InventoryID, 0, -@PQuantity)
	else
		update tb3529 Set PQuantity = PQuantity - @PQuantity Where DivisionID = @DivisionID and 
						WareHouseID = @WareHouseID and InventoryID = @InventoryID
	
	Fetch next from @tb3529_cur into @POrderID, @DivisionID, @WareHouseID, @InventoryID, @PQuantity, @IsPicking
End 

Close @tb3529_cur




GO
--DROP TRIGGER [dbo].[OY3002]
GO
CREATE TRIGGER [dbo].[OY3002]
ON [dbo].[tb3538]
AFTER INSERT
AS
Declare @tb3529_cur as cursor,
	@PQuantity as decimal(28,2),
	@InventoryID as varchar(20),
	@WareHouseID as varchar(20),
	@DivisionID as varchar(20), 
	@IsPicking as tinyint,
	@POrderID varchar(20)

Set @tb3529_cur = cursor scroll keyset for
		Select Ins.POrderID, DivisionID, WareHouseID, InventoryID, OrderQuantity, IsPicking
		From Inserted Ins inner join tb3537 on Ins.POrderID = tb3537.POrderID
		Where Isnull(WareHouseID,'') <> ''

Open @tb3529_cur 
Fetch next from @tb3529_cur into @POrderID, @DivisionID, @WareHouseID, @InventoryID, @PQuantity , @IsPicking
While @@Fetch_Status = 0
Begin
	If @IsPicking <>0 	
		If not exists(Select Top 1 1 From tb3529 Where DivisionID = @DivisionID and WareHouseID = @WareHouseID and
					InventoryID = @InventoryID) 
		Insert tb3529(DivisionID, WarehouseID, InventoryID, SQuantity, PQuantity) values
			(@DivisionID, @WarehouseID, @InventoryID, 0, @PQuantity)
	else
		update tb3529 Set PQuantity = PQuantity + @PQuantity Where DivisionID = @DivisionID and 
			WareHouseID = @WareHouseID and InventoryID = @InventoryID

	 
	Fetch next from @tb3529_cur into @POrderID, @DivisionID, @WareHouseID, @InventoryID, @PQuantity , @IsPicking
End 

Close @tb3529_cur




GO
--DROP TRIGGER [dbo].[OZ3002]
GO
CREATE TRIGGER [dbo].[OZ3002]
ON [dbo].[tb3538]
AFTER UPDATE
AS
Declare @tb3529_cur as cursor,
	@PQuantity_old as decimal(28,2),
	@PQuantity_new as decimal(28,2),
	@InventoryID_old as varchar(20),
	@InventoryID_new as varchar(20),
	@WareHouseID_old as varchar(20),
	@WareHouseID_new as varchar(20),
	@DivisionID as varchar(20),
	@IsPicking_old as tinyint,
	@IsPicking_New as tinyint,
	@POrderID varchar(20)


Set @tb3529_cur = cursor scroll keyset for
		Select Ins.POrderID, DivisionID, Del.WareHouseID, Ins.WareHouseID, Del.InventoryID, Ins.InventoryID,  Del.OrderQuantity, Ins.OrderQuantity, ins.IsPicking, Del.IsPicking
		From Inserted Ins inner join Deleted Del on Ins.TransactionID = Del.TransactionID
			inner join tb3537 on Ins.POrderID = tb3537.POrderID

Open @tb3529_cur 
Fetch next from @tb3529_cur into @POrderID, @DivisionID, @WareHouseID_old,  @WareHouseID_new, 
		@InventoryID_old, @InventoryID_new, @PQuantity_old, @PQuantity_new, @IsPicking_New, @IsPicking_Old
While @@Fetch_Status = 0
Begin
IF @IsPicking_Old <>0	
	If not exists(Select Top 1 1 From tb3529 Where DivisionID = @DivisionID and WareHouseID = @WareHouseID_old and
					InventoryID = @InventoryID_old) 
		Insert tb3529(DivisionID, WarehouseID, InventoryID, SQuantity, PQuantity) values
			(@DivisionID, @WarehouseID_old, @InventoryID_old, 0, -@PQuantity_old)
	else
		update tb3529 Set PQuantity = PQuantity - @PQuantity_old Where DivisionID = @DivisionID and 
						WareHouseID = @WareHouseID_old and InventoryID = @InventoryID_old
	IF @IsPicking_New <>0
	If not exists(Select Top 1 1 From tb3529 Where DivisionID = @DivisionID and WareHouseID = @WareHouseID_new and
						InventoryID = @InventoryID_new) 
		Insert tb3529(DivisionID, WarehouseID, InventoryID, SQuantity, PQuantity) values
			(@DivisionID, @WarehouseID_new, @InventoryID_new, 0, @PQuantity_new)
	else
		update tb3529 Set PQuantity = PQuantity + @PQuantity_new Where DivisionID = @DivisionID and 
						WareHouseID = @WareHouseID_new and InventoryID = @InventoryID_new

	

	Fetch next from @tb3529_cur into @POrderID, @DivisionID, @WareHouseID_old,  @WareHouseID_new, 
		@InventoryID_old, @InventoryID_new, @PQuantity_old, @PQuantity_new, @IsPicking_New, @IsPicking_Old

End 

Close @tb3529_cur






GO
